<?php

/**
 * $Id: adjacency.class.php,v 1.1 2010/04/10 
 *
 *
 * This is a simple class to handle adjacency list model operations within a MySQL database. 
 * The class manages data trees based on the adjacency list  model.
 * Please see: http://en.wikipedia.org/wiki/Adjacency_list
 *
 *
 * Please see examples file for operation
 *
 * LICENSE: Redistribution and use in source and binary forms, with or
 * without modification, are permitted provided that the following
 * conditions are met: Redistributions of source code must retain the
 * above copyright notice, this list of conditions and the following
 * disclaimer. Redistributions in binary form must reproduce the above
 * copyright notice, this list of conditions and the following disclaimer
 * in the documentation and/or other materials provided with the
 * distribution.
 *
 * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESS OR IMPLIED
 * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
 * MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN
 * NO EVENT SHALL CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
 * INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
 * BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS
 * OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR
 * TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE
 * USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH
 * DAMAGE.
 *
 * @package     Adjacency List Model
 * @author      Peter Drinnan <peterd@3da.[dot]com>
 * @copyright   2010 Peter Drinnan
 * @license     http://www.opensource.org/licenses/bsd-license.php
 */
class adjacencyTree {

    // db configuration
    private $config = NULL;
    /**
     * @var used for mysql operations
     */
    public $dbConnectionID;
    /**
     * @var array $refs holds a data reference to the child nodes
     */
    public $dbtablename = '';
    /**
     * @var array $refs holds a data reference to the child nodes
     */
    public $refs = array();
    /**
     * @var array $list holds a data reference to the parent node(s)
     */
    public $list = array();
    /**
     * @var array $idlist holds a list of ids
     */
    public $idlist = array();
    /**
     * @var int used to set initial list key to parent id
     */
    private $initparentid = null;
    /**
     * @var string $id_field points to the key id of the record
     */
    public $id_field = '';
    /**
     * @var string $parent_id_field points to the key parent_id of the record
     */
    public $parent_id_field = '';
    /**
     * @var string $position_field points to the key position of the record
     */
    public $position_field = '';
    /**
     * @var string $level_field points to the key position of the record
     */
    public $level_field = '';
    /**
     * @var string $where_clause specifies the condition to be used where querying the table
     */
    public $where_clause = '';
    /**
     * @var array $tablefields holds all non key fields of the source data  table
     */
    private $tablefields = array();
    /**
     * @var array $deleteids holds a list of ids that can be deleted from a node
     */
    private $deleteids = array();

    /**
     * Constructor
     *
     */
    public function __construct() {
        global $config;
        
        $this->config = $config;
    }

//////////////

    /**
     *  set database connection id
     * @param string
     */
    public function setDBConnectionID($dbconnectionid) {

        $this->dbConnectionID = $dbconnectionid;
    }

//////////////////////////

    /**
     *  set var id_field
     * @param string
     */
    public function setidfield($id_field) {

        $this->id_field = $id_field;
    }

/////////////////

    /**
     *  set var parent_id_field
     * @param string
     */
    public function setparentidfield($parent_id_field) {
        $this->parent_id_field = $parent_id_field;
    }

//////////////////

    /**
     *  set var position_field
     * @param string
     */
    public function setpositionfield($position_field) {
        $this->position_field = $position_field;
    }

/////////////

    /**
     *  set var position_field
     * @param string
     */
    public function setLevelField($level_field) {
        $this->level_field = $level_field;
    }

/////////////

    /**
     *  set var dbtablename
     * @param string
     */
    public function setdbtable($tablename) {
        $this->dbtablename = $tablename;
    }

///////////////////

    /**
     * set var where_clause
     * @param string clause
     */
    public function setWhereClause($clause) {
        $this->where_clause = $clause;
    }

    /**
     * Set the field names for the table omitting key fields
     */
    private function setFieldNames() {
		
		// if the tablefields is already populated, then there is no need to repopulate it again
		if( count($this->tablefields) > 0 ) {
			return;	
		}

        $sql = "SHOW COLUMNS FROM " . $this->dbtablename;
        $result = mysqli_query($this->config, $sql);
		$this->tablefields = array();

        while ($row = mysqli_fetch_array($result)) {

            $fieldname = $row['Field'];

            if ($fieldname != $this->id_field
                &&
                $fieldname != $this->parent_id_field
                &&
                $fieldname != $this->position_field
                &&
                $fieldname != $this->level_field
            ) {
                $this->tablefields[] = $fieldname;
            }
        }
    }

//////////////////

    /**
     * trigger for getFullNodesArray resets list and ref before call
     * @return multidimensional array
     */
    public function getFullNodes($fields = "*") {

        $this->resetdrill = false;
        $this->list = array();  // reinitialize this list
        $this->refs = array();  // reinitialize this list
        $this->initparentid = null; // init all

        return $this->getFullNodesArray(NULL, $fields);
    }

/////////////////

    /**
     * read all data from the source able and put it into a multidimensional array
     * @return multidimensional array
     */
    private function getFullNodesArray($parent = NULL, $fields = '*') {
        
        $this->setFieldNames();

        $sql = "SELECT * FROM " . $this->dbtablename . " " . $this->where_clause . " ORDER BY " . $this->position_field;

        if (is_array($fields)) {
            if (!in_array($this->id_field, $fields)) {
                array_push($fields, $this->id_field);
            }

            if (!in_array($this->parent_id_field, $fields)) {
                array_push($fields, $this->parent_id_field);
            }

            if (!in_array($this->position_field, $fields)) {
                array_push($fields, $this->position_field);
            }

            //print_r($fields);

            $sql = "SELECT " . implode(",", $fields) . " FROM " . $this->dbtablename . " " . $this->where_clause . " ORDER BY " . $this->position_field;
            //echo $sql;
        }

        /* if( $parent != NULL ) {
          $sql = "SELECT * FROM " . $this->dbtablename . " WHERE " . $this->parent_id_field . " = '$parent' ORDER BY ".$this->position_field;
          } */

        $result = mysqli_query($this->config, $sql);

        if (((is_object($this->config)) ? mysqli_error($this->config) : (($___mysqli_res = mysqli_connect_error()) ? $___mysqli_res : false))) {
            echo ((is_object($this->config)) ? mysqli_error($this->config) : (($___mysqli_res = mysqli_connect_error()) ? $___mysqli_res : false));
        }


        while ($data = @mysqli_fetch_assoc($result)) {

            $thisref = &$this->refs[$data[$this->id_field]];

            $thisref[$this->id_field] = $data[$this->id_field];

            $thisref[$this->parent_id_field] = $data[$this->parent_id_field];

            $thisref[$this->position_field] = $data[$this->position_field];

            foreach ($this->tablefields as $val) {
                if (isset($data[$val])) {
                    $thisref[$val] = $data[$val];
                }
            }

            if ($data[$this->parent_id_field] == 0) {
                $this->list[$data[$this->id_field]] = &$thisref;
            } else {
                $this->refs[$data[$this->parent_id_field]]['children'][$data[$this->id_field]] = &$thisref;
            }
        }

        return $this->list;
    }

////////////////////

    /**
     * Not used yet but may come in useful later
     */
    public function getFullNodesIDs($parent) {

        $items = $this->getFullNodesArray($parent);

        return $this->getNodeIds($items);
    }

////////// 

    /**
     * Return all the ids of the children
     * @param $parent_id
     * @return array
     */
    public function getChildNodeIDs($parent) {

        $this->list = array();  // reinitialize this list
        $this->refs = array();  // reinitialize this list
        $this->initparentid = null; // init all

        $items = $this->getChildNodesArray($parent);

        return $this->getNodeIds($items);
    }

////////// 


    /*
     *  gets the nodes for a sibling , trigger for getChildNodesArray resets list and ref before call
     * @return multidimensional array
     */

    public function getSiblingNodes($sibling_id, $fields = "*") {

        $this->list = array();  // reinitialize this list
        $this->refs = array();  // reinitialize this list
        $this->initparentid = null; // init all

        $parent_id = $this->getParentID($sibling_id);

        return $this->getChildNodesArray($parent_id, $fields);
    }

//////////////


    /*
     * trigger for getChildNodesArray resets list and ref before call
     * @return multidimensional array
     */

    public function getChildNodes($parent, $fields = "*", $level = 0) {

        $this->list = array();  // reinitialize this list
        $this->refs = array();  // reinitialize this list
        $this->initparentid = null; // init all

        return $this->getChildNodesArray($parent, $fields, $level);
    }

//////////////


    /*
     * return the parent id of the selected category
     * @param $node_id
     * @return $parent_id
     */

    public function getParentID($node_id) {

        $sql = "SELECT " . $this->parent_id_field . " FROM " . $this->dbtablename . " WHERE " . $this->id_field . " = '$node_id' ";

        $row = mysqli_fetch_row(mysqli_query($this->config, $sql));

        return $row[0];
    }

    /*
     * return the immediate parent data of the selected category
     * @param $node_id
     * @return $array of parent data
     */

    public function getParentData($node_id, $fields = "*") {

        $parent_id = $this->getParentID($node_id);

        $sql = "SELECT * FROM " . $this->dbtablename . " WHERE " . $this->id_field . " = '$parent_id' ";

        if (is_array($fields)) {
            if (!in_array($this->id_field, $fields)) {
                array_push($fields, $this->id_field);
            }

            if (!in_array($this->parent_id_field, $fields)) {
                array_push($fields, $this->parent_id_field);
            }

            if (!in_array($this->position_field, $fields)) {
                array_push($fields, $this->position_field);
            }

            $sql = "SELECT " . implode(",", $fields) . " FROM " . $this->dbtablename . " WHERE " . $this->id_field . " = '$parent_id' ";
        }

        $row = mysqli_fetch_assoc(mysqli_query($this->config, $sql));

        return $row;
    }

    /*
     * return an enumerated array of parent ids for the selected child
     * @param $node_id
     * @return array list of full path parent_ids
     */

    public function getParentIDs($node) {

        $ajdparent_ids = array(); // initialize the idlist

        $has_parent = true;

        while ($has_parent) {

            $node = $this->getParentID($node);

            if ($node > 1) { // over the master id
                $ajdparent_ids[] = $node;
            } else {

                $has_parent = false;
            }
        }


        $ajdparent_ids = array_reverse($ajdparent_ids);

        return $ajdparent_ids;
    }

/////////////


    /*
     * return an enumerated array of parent data for the selected child
     * @param $node_id
     * @return array list of full path parents data
     */

    public function getParentsData($node, $fields = "*") {

        $ajdparent_data = array(); // initialize the idlist

        $nodedata[$this->id_field] = $node;

        $has_parent = true;

        while ($has_parent) {

            $nodedata = $this->getParentData($nodedata[$this->id_field], $fields);

            //if($nodedata[$this->id_field] > 1){ // over the master id
            if ($nodedata[$this->id_field] >= 1) { // over or including the master id
                $ajdparent_data[] = $nodedata;
            } else {

                $has_parent = false;
            }
        }

        return array_reverse($ajdparent_data);
    }

/////////////

    /**
     * Get all the position info required to determine where a category is in terms of the beginning or end of a list
     * Useful if you want to add move up/down arrows
     * @param int node_id
     * @return array
     */
    public function getPositionData($node_id) {

        $cat_count = 0;
        $found_position = null;

        $parent_id = $this->getParentID($node_id);

        $sql = "SELECT * FROM " . $this->dbtablename . " WHERE " . $this->parent_id_field . " = '$parent_id' ORDER BY " . $this->position_field;

        $result = mysqli_query($this->config, $sql);

        while ($row = mysqli_fetch_array($result)) {

            $this_id = $row[$this->id_field];

            if ($node_id == $this_id) {

                $found_position = $cat_count;
            }

            $cat_count++;
        }

        return array("parent_id" => $parent_id, "numcats" => ($cat_count - 1), "position" => $found_position);
    }

//////////////

    /**
     * Moves a node up or down within the parent list
     * @params $node_id, $direction
     * @return null
     */
    public function repositionSibling($node_id, $direction) {

        $direction = strtolower($direction);
        $parent_id = $this->getParentID($node_id);

        // first thing is to clean gaps and set positions for the siblings
        $positioncount = 0;

        $sql = "SELECT * FROM " . $this->dbtablename . " WHERE " . $this->parent_id_field . " = '$parent_id' ORDER BY " . $this->position_field . " ASC";

        $result = mysqli_query($this->config, $sql);

        while ($row = mysqli_fetch_array($result)) {

            $this_id = $row[$this->id_field];

            $sql = "UPDATE " . $this->dbtablename . " SET " . $this->position_field . "='$positioncount' WHERE " . $this->id_field . " = '$this_id'";

            mysqli_query($this->config, $sql);

            $positioncount++;
        }


        $sql = "SELECT * FROM " . $this->dbtablename . " WHERE " . $this->parent_id_field . " = '$parent_id' ORDER BY " . $this->position_field;

        if ($direction == "up")
            $sql .= " ASC";
        else
            $sql .= " DESC";

        $result = mysqli_query($this->config, $sql);

        while ($row = mysqli_fetch_array($result)) {

            $this_id = $row[$this->id_field];
            $this_position = $row[$this->position_field];


            if ($this_id == $node_id) {

                // we need to get the previous id and position
                $sql = "UPDATE " . $this->dbtablename . " SET " . $this->position_field . "='" . $prev_position . "' WHERE " . $this->id_field . " = '$this_id'";
                mysqli_query($this->config, $sql);

                $sql = "UPDATE " . $this->dbtablename . " SET " . $this->position_field . "='" . $this_position . "' WHERE " . $this->id_field . " = '$prev_id'";
                mysqli_query($this->config, $sql);
            }

            $prev_id = $this_id;
            $prev_position = $this_position;
        }
    }

////////////

    /**
     * Get all the nodes that have the same parent .. pretty basic
     */
    public function getSiblings($node_id) {

        $this->setFieldNames();

        $siblings = array();

        $parent_id = $this->getParentID($node_id);

        $where = "";
        if( strpos($this->where_clause, "WHERE") != false ) {
           $where = " WHERE ";
        } else {
           $where = " " . $this->where_clause . " AND  ";
        }

        $sql = sprintf("SELECT * FROM " . $this->dbtablename . $where . $this->parent_id_field . " %s AND " . $this->id_field . " %s ORDER BY " . $this->position_field,
                is_numeric($parent_id) ? " = $parent_id " : 'IS NULL',
                is_numeric($node_id) ? " <> $node_id " : 'IS NOT NULL');

        //echo $sql . "<br /><br />";
        $result = mysqli_query($this->config, $sql) or die(((is_object($this->config)) ? mysqli_error($this->config) : (($___mysqli_res = mysqli_connect_error()) ? $___mysqli_res : false)));

        while ($row = mysqli_fetch_array($result)) {


            $thisref = array($this->id_field => $row[$this->id_field]);

            $thisref[$this->parent_id_field] = $row[$this->parent_id_field];
            $thisref[$this->position_field] = $row[$this->position_field];

            foreach ($this->tablefields as $val) {
                $thisref[$val] = $row[$val];
            }

            $siblings[] = $thisref;
        }

        return $siblings;
    }

//////////////

    /**
     * A more efficient way to get data from child sets
     */
    private function getChildNodesArray($parent, $fields = "*", $level = -1) {

        global $originalparentid;

        if ($this->initparentid == null) {
            $this->initparentid = "$parent"; // make it a string so it isn't null 0
        }

        $this->setFieldNames();
        $levelClause = $level > 0 ? " AND " . $this->level_field . " = " . $level : "";

        $sql = "SELECT * FROM " . $this->dbtablename . " WHERE " . $this->parent_id_field . " = '$parent' $levelClause ORDER BY " . $this->position_field;

        if (is_array($fields)) {
            if (!in_array($this->id_field, $fields)) {
                array_push($fields, $this->id_field);
            }

            if (!in_array($this->parent_id_field, $fields)) {
                array_push($fields, $this->parent_id_field);
            }

            if (!in_array($this->position_field, $fields)) {
                array_push($fields, $this->position_field);
            }

            //print_r($fields);

            $sql = "SELECT " . implode(",", $fields) . " FROM " . $this->dbtablename . " WHERE " . $this->parent_id_field . " = '$parent' $levelClause ORDER BY " . $this->position_field;
            //echo $sql;
        }

        //echo $sql;
        $result = mysqli_query($this->config, $sql);

        while ($row = mysqli_fetch_array($result)) {

            $thisref = &$this->refs[$row[$this->id_field]];
            $thisref[$this->id_field] = $row[$this->id_field];
            $thisref[$this->parent_id_field] = $row[$this->parent_id_field];
            $thisref[$this->position_field] = $row[$this->position_field];

            foreach ($this->tablefields as $val) {
                //$thisref[$val] = $row[$val];
                if (isset($row[$val])) {
                    $thisref[$val] = $row[$val];
                }
            }

            $this->getChildNodesArray($row[$this->id_field], $level + 1);

            if ($this->initparentid == $row[$this->parent_id_field]) { // is this a root node relative to the parent
                $this->list[$row[$this->id_field]] = &$thisref;
            } else {
                $this->refs[$row[$this->parent_id_field]]['children'][$row[$this->id_field]] = &$thisref;
            }
        }

        return $this->list;
    }

////////// 

    /**
     * Get all the data from a single node
     */
    public function getNode($id) {

        $this->setFieldNames();

        $sql = "SELECT * FROM " . $this->dbtablename . " WHERE " . $this->id_field . "='" . $id . "'";

        return mysqli_fetch_assoc(mysqli_query($this->config, $sql));
    }

////////////////

    /**
     * make a simple one dimensional array if ids and filed names
     */
    public function getNodeIds($items) {

        global $nodeids;

        if (count($items) && is_array($items)) {

            foreach ($items as $node_id => $catvals) {
                $nodeids[] = $node_id;

                if (count($catvals['children'])) {
                    $this->getNodeIds($catvals['children']);
                }
            }
        }

        return $nodeids;
    }

////////////////

    /**
     *  Move a node to a new parent. With Adjacency, this single record update will move all child nodes as well
     */
    public function reparentNode($id, $new_parent_id) {

        if ($id != $new_parent_id) {
            $sql = "UPDATE " . $this->dbtablename . " SET " . $this->parent_id_field . "='$new_parent_id' WHERE " . $this->id_field . "='$id'";
            mysqli_query($this->config, $sql);
        }
    }

//////////////

    /**
     * Update a node
     * @return null
     */
    public function updateNode($id, $data) {

        $fnames = array();

        foreach ($data as $key => $val) {
            if ($key != $this->id_field)
                $fnames[] = " $key = '$val' ";
        }

        $sql = "UPDATE " . $this->dbtablename . " SET ";

        $sql .= implode(",", $fnames);

        $sql .= " WHERE " . $this->id_field . " = '$id'";

        mysqli_query($this->config, $sql);
    }

////////////////

    /**
     * Add a node
     * @return id of new node
     */
    public function addNode($parent_id, $data) {

        $fnames = array();
        $fvals = array();

        foreach ($data as $key => $val) {
            $fnames[] = $key;
            $fvals[] = $val;
        }

        $sql = "INSERT INTO " . $this->dbtablename;

        $sql .= " (" . $this->parent_id_field . ",";

        $sql .= implode(",", $fnames);

        $sql .= ") VALUES ('" . $parent_id . "','";

        $sql .= implode("','", $fvals);

        $sql .= "')";

        mysqli_query($this->config, $sql);


        return ((is_null($___mysqli_res = mysqli_insert_id($this->dbConnectionID))) ? false : $___mysqli_res);
    }

////////////////

    /**
     * Add a node next to a sibling
     * @params $sibling_id, $data
     * @return id of new node
     */
    public function addSiblingNode($sibling_id, $data) {

        $parent_id = $this->getParentID($sibling_id);

        return $this->addNode($parent_id, $data);
    }

////////////////

    /**
     * Get all ids for child notes that are to be be deleted
     * @return array
     */
    public function getIndentedNodeIds($items) {

        global $group_indent, $indentedarray;

        $this->setFieldNames();

        if (!is_array($indentedarray))
            $indentedarray = array();

        if (count($items) && is_array($items)) {

            $group_indent++;

            foreach ($items as $node_id => $catvals) {

                $indentedarray[$node_id] = $catvals;

                $indentedarray[$node_id]['indent'] = $group_indent;

                if (count($catvals['children'])) {
                    $this->getIndentedNodeIds($catvals['children']);
                }
            }

            $group_indent--;
        }

        return $indentedarray;
    }

////////////////////

    /**
     * Get all ids for child notes that are to be be deleted
     * @return array
     */
    private function getdeleteids($items, $subloop = false) {

        if ($subloop == false) {
            $this->deleteids = array();
        }

        if (count($items) && is_array($items)) {
            foreach ($items as $node_id => $catvals) {
                $this->deleteids[] = $node_id;

                if (count($catvals['children'])) {
                    $this->getdeleteids($catvals['children'], true);
                }
            }
        }

        return $this->deleteids;
    }

//////////////////

    /**
     * DELETE CATEGORY AND all child nodes
     */
    public function deleteNode($id) {

        global $opcmf;

        $deleteitems = $this->getChildNodes($id);

        $deleteids = $this->getdeleteids($deleteitems);

        $deleteids[] = $id;

        $sql = "DELETE FROM " . $this->dbtablename . " WHERE " . $this->id_field . "='";

        $sql .= implode("' OR " . $this->id_field . "='", $deleteids);

        $sql .= "';";

        mysqli_query($this->config, $sql);
    }

//////////////
}
?>